<?php

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

/**
 * Description of Admin
 *
 * @author Massri
 */
class Application_Model_Admin {
    //put your code here
    
    private $_db;

    public $_loops;
    
    function __construct() {
        $registry = Zend_Registry::getInstance();
        $this->_db = $registry->database;
    }

    

    function addrepeatOrder(){
        //
    }


    function getMember($flag, $pin, $start = "", $limit = "") {

        $sql = "select count(*) as total
                from members where is_repeat_order = '$flag' ";

        if ($pin != "") $sql .= " and member_code like '%$pin%'";

        $result = $this->_db->fetchRow($sql);
        $rows = $result['total'];

        $data = array($rows);



        $sql = "select member_id, member_code, member_name, if(bin_pos=1,'Kiri','Kanan') as bin_pos,
                if(is_repeat_order='Y','Ya','Tidak') as is_repeat_order
                from members where is_repeat_order = '$flag' ";
        
        if ($pin != "") $sql .= " and member_code like '%$pin%' ";

        $sql .= " limit $start, $limit ";

        //echo $sql;
        //die();
        $result = $this->_db->fetchAll($sql);

        array_push($data, $result);

        return $data;
    }


    function setRO($roList, $flag) {

        $this->_db->beginTransaction();

        $ret = array();
        $op = ($flag == 'Y') ? 'Set' : 'Unset';

        try {

            $sql = "update members set is_repeat_order = '$flag' where member_code in ($roList)";
            $res = $this->_db->query($sql);

            $this->_db->commit();



            $ret = array(
                    'code'  => 0,
                    'msg'   => $op.' Repeat Order Sukses'
                );



        }catch (Exception $e)
        {
            $this->_db->rollBack();
            //my_error_handlier($e);
            $arrE = explode(":", $e);
            $arrE2 = explode(" in ", $arrE[1]);
            $errMsg = $arrE2[0];

            $ret = array(
                'code'  => 1,
                'msg'   => $op.' Repeat Order Gagal: '.$errMsg
            );
        }

        return $ret;
    }


    function getOrderTopup($start, $limit) {

        $sql = "select int_value
                from gparams
                where param_name = 'GOLD_PRICE'";

        $result = $this->_db->fetchRow($sql);
        $minBalance = $result['int_value'];

        $sql = "select count(1) as total, FORMAT(sum(td.amt2),0) as jumlah
                from trans_header th, trans_detail td, accounts a, account_bank ab
                where td.trans_hid = th.trans_hid
                and th.trans_type_id = 13 and th.trans_status = 0
                and a.acc_id = ab.acc_id and a.id_reff1=th.user_ref";

        $result = $this->_db->fetchRow($sql);
        $rows = $result['total'];
        $total = $result['jumlah'];

        $data = array($rows, $total);

        $sql = "select th.trans_dtm, td.acc_num1, td.acc_num2, FORMAT(td.amt2,0) as amt_rp, td.amt2, td.descr,
                th.trans_type_id, FORMAT(a.balance_rorder,0) as saldo_ro, FORMAT($minBalance-a.balance_rorder, 0) as kurang_ro,
                CONCAT('<a href=\"".Zend_Controller_Front::getInstance()->getBaseUrl()."/admin/followuptopup/id/', th.trans_hid, '\"><img src=\"".Zend_Controller_Front::getInstance()->getBaseUrl()."/jeasy/themes/gray/icons/redo.png\"></a>') as follow
                from trans_header th, trans_detail td, accounts a, account_bank ab
                where td.trans_hid = th.trans_hid
                and th.trans_type_id = 13 and th.trans_status = 0
                and a.acc_id = ab.acc_id and a.id_reff1=th.user_ref
                order by th.trans_hid
                limit $start, $limit";

        $result = $this->_db->fetchAll($sql);


        array_push($data, $result);


        return $data;
    }


    function detailTopup($id){

        $sql = "select th.trans_hid, th.trans_dtm, td.acc_num1, FORMAT(td.amt2,0) as amt_rp, td.amt2, td.descr,
                th.trans_type_id, td.attr3, if(td.attr3 = '1', 'Transfer', 'Ambil dari saldo e-Money') as jenis_topup
                from trans_header th, trans_detail td, accounts a, account_bank ab
                where th.trans_hid = $id and td.trans_hid = th.trans_hid
                and th.trans_status = 0 and a.acc_id = ab.acc_id and a.id_reff1=th.user_ref";


        $result = $this->_db->fetchRow($sql);

        return $result;

    }


    function prosesTopup($id, $acc_num, $topup, $jenisTopup){
        $this->_db->beginTransaction();

        $result = array();

        try {
           
            /*
             * Insert Topup
             */
            
             if ($jenisTopup == '1'){
                $sql = "update accounts set balance_rorder = balance_rorder+$topup where acc_num = '$acc_num'";
             }else{

                 //Cek Saldonya lagiiii....
                 $sql = "select balance from accounts where acc_num = '$acc_num'";

                 $result = $this->_db->fetchRow($sql);
                 $balance = $result['balance'];

                 if ($topup > $balance ){
                        $result = array(
                            'code'  => 1,
                            'msg'   => 'Top Up gagal! Jumlah Top Up lebih besar dari Saldo e-money ('.number_format($balance, 0).').'
                        );

                        return $result;

                        exit();
                 }

                 $sql = "update accounts set balance = balance-$topup, balance_rorder = balance_rorder+$topup where acc_num = '$acc_num'";
             }

             $resTU = $this->_db->query($sql);

            if ($resTU){

                /*
                 * Update Transaksi
                 */
                $sql = "update trans_header set trans_status = 3 where trans_hid = $id";
                $res = $this->_db->query($sql);

                if ($res){

                    $this->_db->commit();

                    $result = array(
                        'code'  => 0,
                        'msg'   => 'Proses Approval Sukses',
                        'trans_hid'  => $id
                    );

                    return $result;

                }
            }


        }catch (Exception $e)
        {
            $this->_db->rollBack();
            //my_error_handlier($e);
            $arrE = explode(":", $e);
            $arrE2 = explode(" in ", $arrE[1]);
            $errMsg = $arrE2[0];

            $result = array(
                'code'  => 1,
                'msg'   => $errMsg,
                'trans_hid'  => $id
            );

            return $result;
        }
    }


    function gantiPass($username, $passBaru){
        $this->_db->beginTransaction();

        $ret = array();

        try {

            $data = array(
                'pass'   => $passBaru
            );

            $where = array(
                'username = ?' => $username
            );

            $res = $this->_db->update('admin', $data, $where);

            $this->_db->commit();

            $ret = array(
                    'code'  => 0,
                    'msg'   => 'Ganti Password berhasil',
                    'username'  => $username
                );



        }catch (Exception $e)
        {
            $this->_db->rollBack();
            //my_error_handlier($e);
            $arrE = explode(":", $e);
            $arrE2 = explode(" in ", $arrE[1]);
            $errMsg = $arrE2[0];

            $ret = array(
                'code'  => 1,
                'msg'   => $errMsg,
                'username'  => $username
            );
        }

        return $ret;
    }

    function getSMSOutbox($notel, $date1, $date2, $start, $limit){

        $sql = "select count(1) as total
                from msg_outbox
                where STR_TO_DATE(DATE_FORMAT(tgl_proses, '%Y%m%d'),'%Y%m%d') between STR_TO_DATE('$date1', '%Y%m%d') and STR_TO_DATE('$date2', '%Y%m%d')
                "; // and a.id_reff1=th.user_ref";

        if ($notel != ""){
            $sql .= " and notel = '$notel'";
        }

        $result = $this->_db->fetchRow($sql);
        $rows = $result['total'];

        $data = array($rows);

        $sql = "select *
                from msg_outbox
                where STR_TO_DATE(DATE_FORMAT(tgl_proses, '%Y%m%d'),'%Y%m%d') between STR_TO_DATE('$date1', '%Y%m%d') and STR_TO_DATE('$date2', '%Y%m%d') ";

        if ($notel != ""){
            $sql .= " and notel = '$notel'";
        }

        $sql .=  " order by id desc limit $start, $limit
                ";

                
        $result = $this->_db->fetchAll($sql);

        array_push($data, $result);


        return $data;
    }

    function getSMSWording($start, $limit){
        $sql = "select count(1) as total
                from sms_wording";

        $result = $this->_db->fetchRow($sql);
        $rows = $result['total'];

        $data = array($rows);

        $sql = "select *,
                CONCAT('<a href=\"".Zend_Controller_Front::getInstance()->getBaseUrl()."/admin/editwording/id/', id, '\"><img src=\"".Zend_Controller_Front::getInstance()->getBaseUrl()."/jeasy/themes/gray/icons/pencil.png\"></a>') as edit
                from sms_wording
                order by code 
                limit $start, $limit";

        $result = $this->_db->fetchAll($sql);


        array_push($data, $result);


        return $data;
    }

    function getDetailSMSWording($id){
        $sql = "select *
                from sms_wording where id = $id";

        $result = $this->_db->fetchRow($sql);
        
        return $result;
    }


    function editSMSWording($id,$wording){
        
        $this->_db->beginTransaction();

        $ret = array();

        try {

            $data = array(
                'wording'   => $wording
            );

            $where = array(
                'id = ?' => $id
            );

            $res = $this->_db->update('sms_wording', $data, $where);

            $this->_db->commit();

            $ret = array(
                    'code'  => 0,
                    'msg'   => 'Edit SMS Wording berhasil',
                    'username'  => $username
                );



        }catch (Exception $e)
        {
            $this->_db->rollBack();
            //my_error_handlier($e);
            $arrE = explode(":", $e);
            $arrE2 = explode(" in ", $arrE[1]);
            $errMsg = $arrE2[0];

            $ret = array(
                'code'  => 1,
                'msg'   => $errMsg,
                'username'  => $username
            );
        }

        return $ret; 
    }


    function getWeeklyBalance($sn, $date1, $date2, $start, $limit) {

        if ($limit != ""){

            $sql = "select count(1) as total, FORMAT(sum(a.balance),0) as total_balance
                    from members m, accounts a, account_bank ab
                    where a.id_reff1 = m.member_id
                    and ab.acc_id = a.acc_id
                    /*and STR_TO_DATE(DATE_FORMAT(th.trans_dtm, '%Y%m%d'),'%Y%m%d') between STR_TO_DATE('$date1', '%Y%m%d') and STR_TO_DATE('$date2', '%Y%m%d')*/
                    ";

            if ($sn != ""){
                $sql .= " and m.member_code like '%$sn%' ";
            }

            $result = $this->_db->fetchRow($sql);
            $rows = $result['total'];
            $totBalance = $result['total_balance'];

            $data = array($rows, $totBalance);
        }

        $sql = "select m.member_code, m.member_name, ab.bank_name, ab.bank_acc_num, FORMAT(a.balance,0) as balance_rp, a.balance
                from members m, accounts a, account_bank ab
                where a.id_reff1 = m.member_id
                and ab.acc_id = a.acc_id
                /*and STR_TO_DATE(DATE_FORMAT(th.trans_dtm, '%Y%m%d'),'%Y%m%d') between STR_TO_DATE('$date1', '%Y%m%d') and STR_TO_DATE('$date2', '%Y%m%d')*/
                ";

        if ($sn != ""){
            $sql .= " and m.member_code like '%$sn%' ";
        }

        if ($limit != ""){
            $sql .= " limit $start, $limit";
        }


        $result = $this->_db->fetchAll($sql);

        if ($limit != ""){
            array_push($data, $result);
        }else{
            $data = $result;
        }

        //echo "<pre>";
        //print_r($data);
        //exit();

        return $data;
    }


    function getSystemAcc($date1, $date2, $start, $limit){

        $sql = "select count(1) as total, format(sum(balance),0) as sum
                from accounts
                where id_reff1 is null";
        

        $result = $this->_db->fetchRow($sql);
        $rows = $result['total'];
        $sum = $result['sum'];

        $data = array($rows, $sum);

        $sql = "select acc_num, acc_name, format(balance,0) as balance,
                CONCAT('<a href=\"javascript:void(0)\" class=\"easyui-linkbutton\" onclick=\"viewDetail(''', acc_num, ''',''', acc_name ,''')\"><img src=\"".Zend_Controller_Front::getInstance()->getBaseUrl()."/jeasy/themes/gray/icons/detail.png\"></a>') as detail
                from accounts
                where id_reff1 is null";


        $result = $this->_db->fetchAll($sql);

        array_push($data, $result);


        return $data;
    }


    function getDetailKeuangan($accNum, $transType, $date1, $date2, $start, $limit){
       
        $sql = "select trans_dtm, acc_num1, FORMAT(sum(amt1),0) as amt_rp, sum(amt1) as amt1, descr, debkred, FORMAT(sum(debit),0) as debit, FORMAT(sum(kredit),0) as kredit
                from (
                select DATE_FORMAT(trans_dtm, '%Y%m') as trans_dtm, acc_num1,
                if(trans_type_code = '3001', amt2, amt1) as amt1,
                if(attr3 is not null, concat(descr,'<br>', attr3), descr) as descr,
                D_K as debkred,
                if(D_K = 'D', if(trans_type_code = '3001', amt2, amt1),0) as debit,
                if(D_K = 'K', if(trans_type_code = '3001', amt2, amt1),0) as kredit
                from v_transaction where acc_num1 = '$accNum'
                and STR_TO_DATE(DATE_FORMAT(trans_dtm, '%Y%m'),'%Y%m') between STR_TO_DATE('$date1', '%Y%m') and STR_TO_DATE('$date2', '%Y%m') ";

        if ($transType != ""){
            $sql .= " and trans_type_id = $transType ";
        }

        $sql .= " ) xx
                group by trans_dtm, acc_num1, descr, debkred ";

        $sqNum = "select count(1) as total, FORMAT(sum(amt1),0) as jumlah from ( $sql ) zz";

        
        $result = $this->_db->fetchRow($sqNum);
        $rows = $result['total'];
        $total = $result['jumlah'];

        $data = array($rows, $total);



        /*
        $sql = "select STR_TO_DATE(DATE_FORMAT(trans_dtm, '%Y%m'),'%Y%m') as trans_dtm, acc_num1, FORMAT(if(trans_type_code = '3001', amt2, amt1),0) as amt_rp, if(trans_type_code = '3001', amt2, amt1) as amt1,
                if(attr3 is not null, concat(descr,'<br>', attr3), descr) as descr,
                D_K as debkred,
                if(D_K = 'D', FORMAT(if(trans_type_code = '3001', amt2, amt1),0), 0) as debit, if(D_K = 'K', FORMAT(if(trans_type_code = '3001', amt2, amt1),0), 0) as kredit
                from v_transaction where acc_num1 = '$accNum'
                and STR_TO_DATE(DATE_FORMAT(trans_dtm, '%Y%m'),'%Y%m') between STR_TO_DATE('$date1', '%Y%m') and STR_TO_DATE('$date2', '%Y%m') ";

        if ($transType != ""){
            $sql .= " and trans_type_id = $transType ";
        }

        $sql .= "  limit $start, $limit ";
         * 
         */


        $sql .= "  limit $start, $limit ";

        $result = $this->_db->fetchAll($sql);

        array_push($data, $result);


        return $data;
    }


    function editMember($memberId, $memberCode, $memberName, $pin, $sponsorId,
                        $parentId, $registrationDtm, $startMemberId, $levelId, $memberSeq,
                        $parentTreenaryId, $isRepeatOrder, $binPos,
                            $addrSeqId, $addr, $blockNo, $kelurahan, $kecamatan, $city, $zipCode, $country,
                            $contactSeqId, $mobileNo, $phoneNo, $faxNo, $email,
                            $accId, $accNum, $accName, $accLiveDtm, $accStatus,
                            $accBankId, $bankAccNum, $bankName, $memberName, $branchName, $cityName) {

        $this->_db->beginTransaction();

        $ret = array();

        try {

            $data = array(
                'member_name'   => $memberName
            );

            $where = array(
                'member_id = ?' => $memberId
            );

            $resM = $this->_db->update('members', $data, $where);


            // Update Alamat
            $data = array(
                'addr'      => $addr,
                'block_no'  => $blockNo,
                'kelurahan' => $kelurahan,
                'kecamatan' => $kecamatan,
                'city'      => $city,
                'zip_code'  => $zipCode,
                'country'   => $country
            );

            $where = array(
                'addr_seq_id = ?' => $addrSeqId
            );

            $resMA = $this->_db->update('member_address', $data, $where);



            // Update Kontak
            $data = array(
                'mobile_no' => $mobileNo,
                'phone_no'  => $phoneNo,
                'fax_no'    => $faxNo,
                'email'     => $email
            );

            $where = array(
                'contact_seq_id = ?' => $contactSeqId
            );

            $resMC = $this->_db->update('member_contact', $data, $where);



            // Update Account
            $data = array(
                'acc_name'      => $accName
            );

            $where = array(
                'acc_id = ?' => $accId
            );

            $resMAC = $this->_db->update('accounts', $data, $where);



            // Update Account Bank
            $data = array(
                'bank_acc_num'  => $bankAccNum,
                'bank_name'     => $bankName,
                'member_name'   => $memberName,
                'branch_name'   => $branchName,
                'city_name'     => $cityName
            );

            $where = array(
                'acc_bank_id = ?' => $accBankId
            );

            $resAB = $this->_db->update('account_bank', $data, $where);



            $this->_db->commit();

            $ret = array(
                    'code'  => 0,
                    'msg'   => 'Edit member berhasil',
                    'memberId'  => $memberId
                );


        }catch (Exception $e)
        {
            $errormail=1;
            $this->_db->rollBack();
            //my_error_handlier($e);
            $arrE = explode(":", $e);
            $arrE2 = explode(" in ", $arrE[1]);
            $errMsg = $arrE2[0];

            $ret = array(
                'code'  => 1,
                'msg'   => $errMsg,
                'memberId'  => ""
            );
        }

        return $ret;
    }
    
    
    function editPassMember($memberId, $pin) {

        $this->_db->beginTransaction();

        $ret = array();

        try {

            $data = array(
                'pin'   => $pin
            );

            $where = array(
                'member_id = ?' => $memberId
            );

            $resM = $this->_db->update('members', $data, $where);


            
            $this->_db->commit();

            $ret = array(
                    'code'  => 0,
                    'msg'   => 'Edit password member berhasil',
                    'memberId'  => $memberId
                );


        }catch (Exception $e)
        {
            $errormail=1;
            $this->_db->rollBack();
            //my_error_handlier($e);
            $arrE = explode(":", $e);
            $arrE2 = explode(" in ", $arrE[1]);
            $errMsg = $arrE2[0];

            $ret = array(
                'code'  => 1,
                'msg'   => $errMsg,
                'memberId'  => ""
            );
        }

        return $ret;
    }
    
    
    function getWithdrawReport($sn, $stts, $date1, $date2, $start, $limit) {

        if ($limit != ""){

            $sql = "select count(1) as total
                    from trans_header th, trans_detail td, members m
                    where trans_type_id = 11 
                    and td.trans_hid = th.trans_hid
                    and m.member_id = th.user_ref
                    and STR_TO_DATE(DATE_FORMAT(th.trans_dtm, '%Y%m%d'),'%Y%m%d') between STR_TO_DATE('$date1', '%Y%m%d') and STR_TO_DATE('$date2', '%Y%m%d')
                    ";

            if ($sn != ""){
                $sql .= " and m.member_code like '%$sn%' ";
            }
            
            if ($stts != ""){
                if ($stts == 1){
                    $sql .= " and th.trans_status = 0 and td.attr3 is not null ";
                }else{
                    $sql .= " and th.trans_status = $stts ";
                }
            }
            
            $result = $this->_db->fetchRow($sql);
            $rows = $result['total'];

            $data = array($rows);
        }

        $sql = "select trans_dtm, m.member_code, format(td.amt1,0) as amt1_rp, td.amt1, concat('[',ab.bank_acc_num,'] ', ab.bank_name, ' (',ab.member_name,')') as bank_name,
                concat(if(th.trans_status = 3, 'Approved: ', if(th.trans_status = 0, if(td.attr3 is not null, 'Pending: ', 'Antrian'), 'Cancel: ')), COALESCE(td.attr3,' ')) as status
                from trans_header th, trans_detail td, members m, accounts a, account_bank ab
                where trans_type_id = 11 
                and td.trans_hid = th.trans_hid
                and m.member_id = th.user_ref
                and a.acc_num = td.acc_num1
                and ab.acc_id = a.acc_id
                and STR_TO_DATE(DATE_FORMAT(th.trans_dtm, '%Y%m%d'),'%Y%m%d') between STR_TO_DATE('$date1', '%Y%m%d') and STR_TO_DATE('$date2', '%Y%m%d')
                ";

        if ($sn != ""){
            $sql .= " and m.member_code like '%$sn%' ";
        }
        
        if ($stts != ""){
            if ($stts == 0){
                $sql .= " and th.trans_status = 0 and td.attr3 is null ";
            }elseif ($stts == 1){ //pending
                $sql .= " and th.trans_status = 0 and td.attr3 is not null ";
            }else{
                $sql .= " and th.trans_status = $stts ";
            }
        }

        if ($limit != ""){
            $sql .= " limit $start, $limit";
        }


        $result = $this->_db->fetchAll($sql);

        if ($limit != ""){
            array_push($data, $result);
        }else{
            $data = $result;
        }

        //echo "<pre>$sql";
        //print_r($data);
        //exit();

        return $data;
    }
    
    function rewardList() {

        
        $sql = "select * from p_bonus_reward order by min_point";

        $result = $this->_db->fetchAll($sql);
        
        return $result;
    }
    
    function getRewardReport($sn, $reward, $date1, $date2, $start, $limit) {

        if ($limit != ""){

            $sql = "select count(1) as total
                    from t_bonus_reward br, members m
                    where m.member_id = br.member_id
                    and STR_TO_DATE(DATE_FORMAT(br.period, '%Y%m'),'%Y%m') = STR_TO_DATE('$date1', '%Y%m')";

            if ($sn != ""){
                $sql .= " and m.member_code like '%$sn%' ";
            }
            
            if ($reward != ""){
                $sql .= " and br.class_name = '$reward' ";
            }

            $result = $this->_db->fetchRow($sql);
            $rows = $result['total'];

            $data = array($rows);
        }

        $sql = "select br.period, m.member_code, reward_name, concat(descr,' ',IF(pic is not null and pic <> '', concat('<br/><img src=\"".Zend_Controller_Front::getInstance()->getBaseUrl()."/img/', pic,'\"/>'),'')) as descr
                from t_bonus_reward br, members m, p_bonus_reward pr
                where m.member_id = br.member_id and pr.reward_name = br.class_name
                and STR_TO_DATE(DATE_FORMAT(br.period, '%Y%m'),'%Y%m') = STR_TO_DATE('$date1', '%Y%m') ";

        if ($sn != ""){
            $sql .= " and m.member_code like '%$sn%' ";
        }
        
        if ($reward != ""){
            $sql .= " and br.class_name = '$reward' ";
        }

        if ($limit != ""){
            $sql .= " limit $start, $limit";
        }



        $result = $this->_db->fetchAll($sql);

        if ($limit != ""){
            array_push($data, $result);
        }else{
            $data = $result;
        }

        //echo "<pre>";
        //print_r($data);
        //exit();

        return $data;
    }


    function getPertumbuhanBonus($jenis, $date1, $date2, $start, $limit){

        if ($limit != ""){
            $sql = "select count(1) as total from( ";
            
            //Bonus Pasangan
            if ($jenis == 0 || $jenis == 1){
                $sql .= " select period, 1 as jenis
                        from t_bonus_pasangan
                        where total_bonus > 0 and STR_TO_DATE(DATE_FORMAT(period, '%Y%m%d'),'%Y%m%d') between STR_TO_DATE('$date1', '%Y%m%d') and STR_TO_DATE('$date2', '%Y%m%d') ";
            }
            
            //Bonus Titik
            if ($jenis == 0 || $jenis == 2){
                if ($jenis == 0){
                    $sql .= " union ";
                }
                
                $sql .= " select period, 2 as jenis
                        from t_bonus_xtitik 
                        where jml_bonus > 0 and STR_TO_DATE(DATE_FORMAT(period, '%Y%m%d'),'%Y%m%d') between STR_TO_DATE('$date1', '%Y%m%d') and STR_TO_DATE('$date2', '%Y%m%d') ";
            }
            
            //Bonus Xtrims
            if ($jenis == 0 || $jenis == 3){
                if ($jenis == 0){
                    $sql .= " union ";
                }

                $sql .= " select period, 3 as jenis
                        from t_bonus_xtrims 
                        where jml_bonus > 0 and STR_TO_DATE(DATE_FORMAT(period, '%Y%m%d'),'%Y%m%d') between STR_TO_DATE('$date1', '%Y%m%d') and STR_TO_DATE('$date2', '%Y%m%d') ";
            }


            //Bonus member
            if ($jenis == 0 || $jenis == 4){
                if ($jenis == 0){
                    $sql .= " union ";
                }

                $sql .= " select date_format(registration_dtm,'%Y%m%d') period, 4 as jenis
                        from members
                        where STR_TO_DATE(DATE_FORMAT(registration_dtm, '%Y%m%d'),'%Y%m%d') between STR_TO_DATE('$date1', '%Y%m%d') and STR_TO_DATE('$date2', '%Y%m%d') ";
            }

            $sql .= " group by period, jenis) a"; // and a.id_reff1=th.user_ref";

            $result = $this->_db->fetchRow($sql);
            $rows = $result['total'];

            $data = array($rows);
        }


        
        $sql = "select period, jenis, FORMAT(total_bonus,0) as total_bonus,
                FORMAT(sisa_bonus,0) as sisa_bonus 
                from ( ";
        
        // Bonus Pasangan
        if ($jenis == 0 || $jenis == 1){
            $sql .= " select period, 'Pasangan' as jenis, COALESCE(sum(total_bonus),0) as total_bonus,
                    COALESCE(sum(sisa_bonus),0) as sisa_bonus
                    from t_bonus_pasangan
                    where STR_TO_DATE(DATE_FORMAT(period, '%Y%m%d'),'%Y%m%d') between STR_TO_DATE('$date1', '%Y%m%d') and STR_TO_DATE('$date2', '%Y%m%d')
                    group by period ";
        }

        //Bonus Titik
        if ($jenis == 0 || $jenis == 2){
            if ($jenis == 0){
                $sql .= " union ";
            }

            $sql .= " select period, 'Titik' as jenis, COALESCE(sum(jml_bonus),0) as total_bonus,
                    0 as sisa_bonus
                    from t_bonus_xtitik
                    where STR_TO_DATE(DATE_FORMAT(period, '%Y%m%d'),'%Y%m%d') between STR_TO_DATE('$date1', '%Y%m%d') and STR_TO_DATE('$date2', '%Y%m%d')
                    group by period ";
        }

        //Bonus Xtrims
        if ($jenis == 0 || $jenis == 3){
            if ($jenis == 0){
                $sql .= " union ";
            }

            $sql .= " select period, 'Xtrims' as jenis, COALESCE(sum(jml_bonus),0) as total_bonus,
                    0 as sisa_bonus
                    from t_bonus_xtrims
                    where STR_TO_DATE(DATE_FORMAT(period, '%Y%m%d'),'%Y%m%d') between STR_TO_DATE('$date1', '%Y%m%d') and STR_TO_DATE('$date2', '%Y%m%d')
                    group by period ";
        }


        //Bonus Member
        if ($jenis == 0 || $jenis == 4){
            if ($jenis == 0){
                $sql .= " union ";
            }

            $sql .= "select date_format(registration_dtm,'%Y%m%d') as period, 'Member' as jenis, count(member_id) as total_bonus, max(0) as sisa_bonus
                    from members
                    where STR_TO_DATE(DATE_FORMAT(registration_dtm, '%Y%m%d'),'%Y%m%d') between STR_TO_DATE('$date1', '%Y%m%d') and STR_TO_DATE('$date2', '%Y%m%d')
                    group by date_format(registration_dtm,'%Y%m%d')";
        }

        $sql .=  ") xx where total_bonus > 0 order by jenis, period desc ";

        if ($limit != ""){
            $sql .=  " limit $start, $limit ";
        }

        $result = $this->_db->fetchAll($sql);

        if ($limit != ""){
            array_push($data, $result);
        }else{
            $data = $result;
        }

        return $data;
    }


    public function genSN($num)
    {
        $dataSN = array();
        $cnt = 0;
        $sn = new Application_Model_Sn();
        
        for ($i=0;$i<$num;$i++){
            $res = $sn->genSN();
            $res = explode(",", $res);
            array_push($dataSN, array("SN" => $res[0], "PIN" => $res[1]));
            $cnt++;
        }

        $data = array($cnt);
        array_push($data, $dataSN);
        return $data;
    }

    public function aktifasiMemberPasif($memberId){

        $memberModel = new Application_Model_Member();
        $dataMember = $memberModel->getMemberPasifDetail($memberId);
        $sponsorId = $dataMember['sponsor_id'];

        //get Parent dan Posisi
        /*$sql = "select m.member_id, COALESCE(orig_sumki,0) as orig_sumki,
                COALESCE(orig_sumka,0) as orig_sumka
                from members m, t_bonus_pasangan tb
                where (m.sponsor_id = $sponsorId or m.parent_id = $sponsorId)
                and tb.member_id = m.member_id
                and (COALESCE(orig_sumki,0) = 0 or COALESCE(orig_sumka,0) = 0)
                order by m.member_seq limit 1";

        
        $result = $this->_db->fetchRow($sql);
        
        if (count($result) > 0){
            $parentId = $result['member_id'];
            $binPos = ($result['orig_sumki'] == 0) ? 1 : 2;
        }else{
            $parentId = $sponsorId;
            $binPos = 1;
        }
         * 
         */

        $sql = "select getParentPasif($sponsorId) as parentPos";
        $result = $this->_db->fetchRow($sql);
        $data = explode('|', $result['parentPos']);
        $parentId = $data[0];
        $binPos = $data[1];

        $startMemberId = "-1"; //Flag utk aktifasi member pasif

        $startDat   = new Zend_Db_Expr('NOW()');
        $addMember = $memberModel->addMember($dataMember['member_code'], $dataMember['member_name'], $dataMember['pin'],
                    $dataMember['sponsor_id'], $dataMember['sponsor_code'],
                    $parentId, "", $startMemberId, "", "", "", "", $binPos,
                        $dataMember['addr'], $dataMember['block_no'], $dataMember['kelurahan'], $dataMember['kecamatan'],
                        $dataMember['city'], $dataMember['zip_code'], $dataMember['country'], $startDat, "",
                        $dataMember['mobile_no'], $dataMember['phone_no'], $dataMember['fax_no'], $dataMember['email'],
                        $dataMember['acc_num'], $dataMember['acc_name'], $startDat, "", 0, 0,
                        $dataMember['bank_acc_num'], $dataMember['bank_name'], $dataMember['member_name'], $dataMember['branch_name'],
                        $dataMember['city_name'], $dataMember['id_card'], $dataMember['ahli_waris']);

        
        if($addMember['code'] == 0){

            $this->_db->beginTransaction();

            $sql = "delete from tmp_account_bank where acc_id = (select acc_id from tmp_accounts where id_reff1 = $memberId)";
            $res = $this->_db->query($sql);

            $sql = "delete from tmp_accounts where id_reff1 = $memberId";
            $res = $this->_db->query($sql);

            $sql = "delete from tmp_member_address where member_id = $memberId";
            $res = $this->_db->query($sql);

            $sql = "delete from tmp_member_contact where member_id = $memberId";
            $res = $this->_db->query($sql);

            $sql = "delete from tmp_members where member_id = $memberId";
            $res = $this->_db->query($sql);

            $this->_db->commit();
        }

        return $addMember;

    }


    function getROReport($sn, $stts, $date1, $date2, $start, $limit) {

        if ($limit != ""){

            $sql = "select count(1) as total
                    from v_transaction v, members m
                    where v.trans_type_code = '2005'
                    and m.member_id = v.user_ref
                    and STR_TO_DATE(DATE_FORMAT(v.trans_dtm, '%Y%m%d'),'%Y%m%d') 
                    between STR_TO_DATE('$date1', '%Y%m%d') and STR_TO_DATE('$date2', '%Y%m%d') ";

            if ($sn != ""){
                $sql .= " and m.member_code like '%$sn%' ";
            }

            if ($stts != ""){
                $sql .= " and v.trans_status = $stts ";
            }

            $result = $this->_db->fetchRow($sql);
            $rows = $result['total'];

            $data = array($rows);
        }

        $sql = "select trans_dtm, m.member_code, member_name, 
                concat(addr,' ', block_no, ', ', kelurahan, ', ', kecamatan, ', ', city, ' ', zip_code, ', ', country) as addr,
                v.trans_hid
                from v_transaction v, members m,  member_address a
                where v.trans_type_code = '2005'
                and m.member_id = v.user_ref and a.member_id = m.member_id
                and STR_TO_DATE(DATE_FORMAT(v.trans_dtm, '%Y%m%d'),'%Y%m%d')
                between STR_TO_DATE('$date1', '%Y%m%d') and STR_TO_DATE('$date2', '%Y%m%d') ";

        if ($sn != ""){
            $sql .= " and m.member_code like '%$sn%' ";
        }

        if ($stts != ""){
            $sql .= " and v.trans_status = $stts ";
        }

        if ($limit != ""){
            $sql .= " limit $start, $limit";
        }


        $result = $this->_db->fetchAll($sql);

        if ($limit != ""){
            array_push($data, $result);
        }else{
            $data = $result;
        }

       
        return $data;
    }


    function setStatusRO($roList, $flag) {

        $this->_db->beginTransaction();

        $ret = array();

        try {

            $sql = "update members set is_repeat_order = 'N'
                    where member_id in (select user_ref from v_transaction where trans_type_id = 16 and trans_status = 3 ";

            if ($flag == '1'){
                $sql .= " and trans_hid in ($roList)";
            }

            $sql .= ")";


            $res = $this->_db->query($sql);


            $sql = "update trans_header set trans_status = 9 where trans_type_id = 16 and trans_status = 3 ";

            if ($flag == '1'){
                $sql .= " and trans_hid in ($roList)";
            }

            $res = $this->_db->query($sql);

            $this->_db->commit();



            $ret = array(
                    'code'  => 0,
                    'msg'   => 'Update status pengiriman berhasil'
                );



        }catch (Exception $e)
        {
            $this->_db->rollBack();
            //my_error_handlier($e);
            $arrE = explode(":", $e);
            $arrE2 = explode(" in ", $arrE[1]);
            $errMsg = $arrE2[0];

            $ret = array(
                'code'  => 1,
                'msg'   => 'Update status pengiriman gagal: '.$errMsg
            );
        }

        return $ret;
    }


    function sendSMS($tlp, $pesan) {

        $this->_db->beginTransaction();

        $ret = array();

        try {

            /*
             * Insert msg outbox
             */
            $data = array(
                'notel' => $tlp,
                'msg' => $pesan,
                'status'  => 'F',
                'tgl_proses' => new Zend_Db_Expr('NOW()')
            );

            $this->_db->insert('msg_outbox',$data);

            $this->_db->commit();

            $ret = array(
                    'code'  => 0,
                    'msg'   => 'SMS berhasil diantrikan'
                );



        }catch (Exception $e)
        {
            $this->_db->rollBack();
            //my_error_handlier($e);
            $arrE = explode(":", $e);
            $arrE2 = explode(" in ", $arrE[1]);
            $errMsg = $arrE2[0];

            $ret = array(
                'code'  => 1,
                'msg'   => 'Kirim SMS gagal: '.$errMsg
            );
        }

        return $ret;
    }


}

?>
